﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using KPIReport.Library;
using KPIReport.Library.DomainObject.Administration.Catalog;
using KPIReport.Library.Enum;
using KPIReport.Library.Interfaces;

namespace KPIReport.Library.DataAccess.Administration.Catalog
{
   public partial class transaksiDNdtlRepo
    {
       public bool SaveUpload(List<transaksiDNdtl> domains)
       {
           bool result = false;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               conn.Open();
               SqlTransaction txn = conn.BeginTransaction();
               try
               {
                   domains.ForEach(delegate(transaksiDNdtl domain)
                   {
                       SqlCommand cmd = conn.CreateCommand();
                       cmd.CommandText = @"INSERT INTO dbo.transaksiDNdtl_temp(
                         [Customer Code]
                        ,[Salesman Code]
                        ,[Transaction Number]
                        ,[Transaction Date]
                        ,[Product Code]
                        ,[Product Description]
                        ,[Product Index]
                        ,[Product Quantity]
                        ,[UOM]
                        ,[Default UOM]
                        ,[Product List Price]
                        ,[UOM List Price]
                        ,[Gross Amount]
                        ,[Net Amount]
                        ,[Amount After Tax]
                        ,[Customer Discount Amount]
                        ,[Reason Code]
                        ,[Reason Description]
                        ,[Case_Pallet_Tr_Dn]
                        ,[DSP_Case_Tr_Dn]
                        ,[EA_Case_Tr_Dn]
                        ,[Qty_EADn]
                        ,[Qty_Karton_Dn]
                        ) 
                        VALUES(
                        @Customer_Code
                        , @Salesman_Code
                        , @Transaction_Number
                        , @Transaction_Date
                        , @Product_Code
                        , @Product_Description
                        , @Product_Index
                        , @Product_Quantity
                        , @UOM
                        , @Default_UOM
                        , @Product_List_Price
                        , @UOM_List_Price
                        , @Gross_Amount
                        , @Net_Amount
                        , @Amount_After_Tax
                        , @Customer_Discount_Amount
                        , @Reason_Code
                        , @Reason_Description
                        , @CasePalletTrDn
                        , @DSPCaseTrDn
                        , @EACaseTrDn
                        , @QtyEADn
                        , @QtyKartonDn)";
                       cmd.CommandType = CommandType.Text;

                       //cmd.CommandText = "INSERT INTO dbo.transaksiDNdtl(Customer Code, Salesman Code, Transaction Number, Transaction Date, Product Code, Product Description, Product Index, Product Quantity, UOM, Default UOM, Product List Price, UOM List Price, Gross Amount, Net Amount, Amount After Tax, Customer Discount Amount, Reason Code, Reason Description, Case_Pallet_Tr_Dn, DSP_Case_Tr_Dn, EA_Case_Tr_Dn, Qty_EADn, Qty_Karton_Dn) VALUES(@Customer Code, @Salesman Code, @Transaction Number, @Transaction Date, @Product Code, @Product Description, @Product Index, @Product Quantity, @UOM, @Default UOM, @Product List Price, @UOM List Price, @Gross Amount, @Net Amount, @Amount After Tax, @Customer Discount Amount, @Reason Code, @Reason Description, @CasePalletTrDn, @DSPCaseTrDn, @EACaseTrDn, @QtyEADn, @QtyKartonDn)";
                       //cmd.CommandText = "SP_transaksiDNdtl_Insert";
                       //cmd.CommandType = CommandType.StoredProcedure;
                       
                       cmd.Parameters.Add("@Customer_Code", SqlDbType.NVarChar).Value = (object)domain.Customer_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Salesman_Code", SqlDbType.NVarChar).Value = (object)domain.Salesman_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Transaction_Number", SqlDbType.NVarChar).Value = (object)domain.Transaction_Number ?? DBNull.Value;
                       cmd.Parameters.Add("@Transaction_Date", SqlDbType.DateTime).Value = (object)domain.Transaction_Date ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Code", SqlDbType.NVarChar).Value = (object)domain.Product_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Description", SqlDbType.NVarChar).Value = (object)domain.Product_Description ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Index", SqlDbType.Float).Value = (object)domain.Product_Index ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Quantity", SqlDbType.Float).Value = (object)domain.Product_Quantity ?? DBNull.Value;
                       cmd.Parameters.Add("@UOM", SqlDbType.NVarChar).Value = (object)domain.UOM ?? DBNull.Value;
                       cmd.Parameters.Add("@Default_UOM", SqlDbType.NVarChar).Value = (object)domain.Default_UOM ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_List_Price", SqlDbType.Float).Value = (object)domain.Product_List_Price ?? DBNull.Value;
                       cmd.Parameters.Add("@UOM_List_Price", SqlDbType.Float).Value = (object)domain.UOM_List_Price ?? DBNull.Value;
                       cmd.Parameters.Add("@Gross_Amount", SqlDbType.Float).Value = (object)domain.Gross_Amount ?? DBNull.Value;
                       cmd.Parameters.Add("@Net_Amount", SqlDbType.Float).Value = (object)domain.Net_Amount ?? DBNull.Value;
                       cmd.Parameters.Add("@Amount_After_Tax", SqlDbType.Float).Value = (object)domain.Amount_After_Tax ?? DBNull.Value;
                       cmd.Parameters.Add("@Customer_Discount_Amount", SqlDbType.Float).Value = (object)domain.Customer_Discount_Amount ?? DBNull.Value;
                       cmd.Parameters.Add("@Reason_Code", SqlDbType.NVarChar).Value = (object)domain.Reason_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Reason_Description", SqlDbType.NVarChar).Value = (object)domain.Reason_Description ?? DBNull.Value;
                       cmd.Parameters.Add("@CasePalletTrDn", SqlDbType.Float).Value = (object)domain.CasePalletTrDn ?? DBNull.Value;
                       cmd.Parameters.Add("@DSPCaseTrDn", SqlDbType.Float).Value = (object)domain.DSPCaseTrDn ?? DBNull.Value;
                       cmd.Parameters.Add("@EACaseTrDn", SqlDbType.Float).Value = (object)domain.EACaseTrDn ?? DBNull.Value;
                       cmd.Parameters.Add("@QtyEADn", SqlDbType.Float).Value = (object)domain.QtyEADn ?? DBNull.Value;
                       cmd.Parameters.Add("@QtyKartonDn", SqlDbType.Float).Value = (object)domain.QtyKartonDn ?? DBNull.Value;

                       cmd.Transaction = txn;
                       cmd.ExecuteNonQuery();
                       cmd.Dispose();
                   });

                   SqlCommand cmdDelete = conn.CreateCommand();
                   cmdDelete.CommandText = @"DELETE transaksidndtl WHERE [Transaction Number] IN (SELECT [Transaction Number] FROM transaksidndtl_temp)";
                   cmdDelete.CommandType = CommandType.Text;

                   cmdDelete.Transaction = txn;
                   cmdDelete.ExecuteNonQuery();
                   cmdDelete.Dispose();

                   SqlCommand cmdInsert = conn.CreateCommand();
                   StringBuilder sb = new StringBuilder();

                    sb.AppendLine(" insert into transaksiDNdtl ([Customer Code],[Salesman Code],[Transaction Number],[Transaction Date], [Product Code],[Product Description],[Product Index],[Product Quantity],UOM,[Default UOM],[Product List Price],[UOM List Price],[Gross Amount],[net Amount],[Amount After Tax],[Customer Discount Amount],[Reason Code],[Reason Description])");
                    sb.AppendLine(" select [Customer Code],[Salesman Code],[Transaction Number],[Transaction Date] ,[Product Code],[Product Description],[Product Index],[Product Quantity],UOM,[Default UOM],[Product List Price],[UOM List Price],[Gross Amount],[net Amount],[Amount After Tax],[Customer Discount Amount],[Reason Code],[Reason Description] from transaksiDNdtl_temp");
                    sb.AppendLine(" where YEAR([transaction date]) in (select distinct YEAR([transaction date]) from transaksiDNdtl_temp) and MONTH([transaction date]) in(select distinct MONTH([transaction date]) from transaksiDNdtl_temp)");


                   cmdInsert.CommandText = sb.ToString();
                   cmdInsert.CommandType = CommandType.Text;
                   cmdInsert.Transaction = txn;
                   cmdInsert.ExecuteNonQuery();
                   cmdInsert.Dispose();

                   result = true;
                   txn.Commit();
               }
               catch (Exception ex)
               {
                   txn.Rollback();
                   throw (ex);
               }
               finally
               {
                   if (conn.State == ConnectionState.Open) conn.Close();
               }


           }
           return result;
       }
    }
}
